package com.line.dao;


import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.stereotype.Repository;

import com.base.dao.TemplateDaoImpl;
import com.data.entity.ChargeDay;
import com.data.entity.ChargeMin;
import com.data.entity.ChargeMonth;
import com.data.entity.ChargeWeek;

@Repository("lineDao")
public class LineDaoImpl extends TemplateDaoImpl<ChargeMin, Integer>implements LineDao{
	/**
	 * 整线下所有设备电度电费小时统计
	 * @param belongTime
	 * @return
	 */
	@Override
	public List<ChargeMin> lineChargesByDay(String startTime,String endTime) {
		StringBuffer sb=new StringBuffer("select equId,COALESCE(SUM(relativeComp),0)relativeComp, COALESCE(SUM(charge),0) charge from chargeMin where");
		sb.append(" DATE_FORMAT(belongTime,'%Y-%m-%d %H:%i')>:startTime and  DATE_FORMAT(belongTime,'%Y-%m-%d %H:%i')<=:endTime  GROUP BY equId");
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("startTime", startTime);
		params.put("endTime", endTime);
		return jt.query(sb.toString(), params, ParameterizedBeanPropertyRowMapper.newInstance(ChargeMin.class));
	}
	/**
	 * 整线下所有设备电度电费日统计
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	@Override
	public List<ChargeDay> lineChargesByDate(String startDate,String endDate) {
		StringBuffer sb=new StringBuffer("select equId,COALESCE(SUM(relativeComp),0)relativeComp, COALESCE(SUM(charge),0) charge from chargeDay where");
		sb.append(" DATE_FORMAT(belongDate,'%Y-%m-%d')>=:startDate and  DATE_FORMAT(belongDate,'%Y-%m-%d')<=:endDate  GROUP BY equId");
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("startDate", startDate);
		params.put("endDate", endDate);
		return jt.query(sb.toString(), params, ParameterizedBeanPropertyRowMapper.newInstance(ChargeDay.class));
	}
	/**
	 * 整线下的所有设备周电度电费统计
	 * @param belongYear
	 * @param startWeek
	 * @param endWeek
	 * @return
	 */
	@Override
	public List<ChargeWeek> lineChargesByWeek(int belongYear, int startWeek,int endWeek) {
		StringBuffer sb=new StringBuffer("select equId,COALESCE(SUM(relativeComp),0)relativeComp, COALESCE(SUM(charge),0) charge from chargeWeek where");
		sb.append(" belongYear=:belongYear and  belongWeek>=:startWeek and  belongWeek<=:endWeek GROUP BY equId");
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("belongYear", belongYear);
		params.put("startWeek", startWeek);
		params.put("endWeek", endWeek);
		return jt.query(sb.toString(), params, ParameterizedBeanPropertyRowMapper.newInstance(ChargeWeek.class));
	}
	/**
	 * 整线电度电费月统计
	 * @param startMonth
	 * @param endMonth
	 * @return
	 */
	@Override
	public List<ChargeMonth> lineChargesByMonth(String startMonth,String endMonth) {
		StringBuilder sb=new StringBuilder("select equId,COALESCE(SUM(relativeComp),0)relativeComp, COALESCE(SUM(charge),0) charge from chargeMonth where ");
		sb.append(" belongDate>=:startMonth and belongDate<=:endMonth group by equId ");
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("startMonth", startMonth);
		params.put("endMonth", endMonth);
		return jt.query(sb.toString(), params, ParameterizedBeanPropertyRowMapper.newInstance(ChargeMonth.class));
	}
}
